IF OBJECT_ID ( 'CTM_TransferDataSP', 'P' ) IS NOT NULL 
    DROP PROCEDURE CTM_TransferDataSP;
GO

CREATE PROCEDURE CTM_TransferDataSP
AS
  BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

	-- Update existing users
	UPDATE 
		[AspNetUsers]
	SET
		[AspNetUsers].Avatar = [TempUsers].Avatar,
		[AspNetUsers].DisplayName = [TempUsers].DisplayName,
		[AspNetUsers].Email = [TempUsers].Email,
		[AspNetUsers].TransferredOn = [TempUsers].TransferredOn,
		[AspNetUsers].EmpNo = [TempUsers].EmpNo
	FROM
		[AspNetUsers]
	INNER JOIN
		[TempUsers]
	ON
		[AspNetUsers].Id = [TempUsers].Id;

	-- Insert new users
	INSERT [dbo].[AspNetUsers] ([Id], [Avatar], [DisplayName], [NTID], [Email], [EmailConfirmed], [PasswordHash], [SecurityStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEndDateUtc], [LockoutEnabled], [AccessFailedCount], [UserName], [TransferredOn], [EmpNo]) 
	SELECT [Id], [Avatar], [DisplayName], [NTID], [Email], [EmailConfirmed], [PasswordHash], [SecurityStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEndDateUtc], [LockoutEnabled], [AccessFailedCount], [UserName], [TransferredOn], [EmpNo]
	FROM TempUsers
	--WHERE [EmpNo] NOT IN (SELECT [EmpNo] FROM [AspNetUsers]);
	WHERE [NTID] NOT IN (SELECT [NTID] FROM [AspNetUsers]);
	
	-- ORGS
	INSERT [dbo].[Orgs] ([Id], [Name], [Description], [Path], [IsDeleted], [CreatedOn], [CreatedBy], [ModifiedOn], [ModifiedBy], [OrgTypeId], [TransferredOn]) 
	SELECT [Id], [Name], [Description], [Path], [IsDeleted], [CreatedOn], [CreatedBy], [ModifiedOn], [ModifiedBy], [OrgTypeId], [TransferredOn]
	FROM [TempOrgs]
	WHERE [Name] NOT IN (SELECT Name FROM [Orgs]);

	-- UPDATE
	UPDATE [Orgs] 
	SET [Orgs].[ManagerId] = o.[ManagerId]
	FROM [Orgs] u
	INNER JOIN [TempOrgs] o
	ON u.Id = o.Id AND o.ManagerId IS NOT NULL AND u.ManagerId != o.ManagerId

	UPDATE [Orgs] 
	SET [Orgs].ParentId = o2.ParentId
	FROM [Orgs] o1
	INNER JOIN [TempOrgs] o2
	ON o1.Name = o2.Name AND o2.ParentId != '00000000-0000-0000-0000-000000000000'

	UPDATE [AspNetUsers] 
	SET [AspNetUsers].[ManagerId] = e.[ManagerId]
	FROM [AspNetUsers] u
	INNER JOIN TempUsers e
	ON u.Id = e.Id AND e.ManagerId IS NOT NULL AND u.ManagerId != e.ManagerId

	UPDATE [AspNetUsers] 
	SET [AspNetUsers].[OrgId] = e.[OrgId]
	FROM [AspNetUsers] u
	INNER JOIN TempUsers e
	ON u.Id = e.Id AND e.[OrgId] != '00000000-0000-0000-0000-000000000000'

	-- Update users DisplayName
	UPDATE [AspNetUsers]
	SET	[AspNetUsers].DisplayName = CASE WHEN [AspNetUsers].DisplayName NOT LIKE '%(RBEI/%' THEN [AspNetUsers].DisplayName + ' (' + [Orgs].Name + ')' ELSE [AspNetUsers].DisplayName END
	FROM [AspNetUsers]
	INNER JOIN [Orgs]
	ON [AspNetUsers].OrgId = [Orgs].Id;
      
  END;
